Você acabou de conseguir um novo emprego como analista de dados para o estado de Massachusetts, trabalhando no Departamento de Educação Elementar e Secundária (ou DEES, para abreviar!). O DEES supervisiona o sistema de educação pública do estado. Uma responsabilidade do DEES é garantir que todas as crianças tenham uma educação de qualidade – uma educação em que encontrem professores experientes, tenham acesso a uma abundância de recursos e, em última análise, formem-se tendo cumprido todos os requisitos do estado. Suas habilidades em SQL têm um papel a desempenhar nesses objetivos elevados!

As informações estão contidas no banco de dados dees.db BAIXAR

Schema

Em Massachusetts, a educação pública é delegada aos distritos, um tipo de governo escolar frequentemente associado a uma cidade individual. Esses distritos, por sua vez, contêm muitas escolas individuais. Considere o diagrama de entidade-relacionamento abaixo, que codifica a relação entre distritos, escolas e outros dados que o DEES recolhe.

Dentro de dese.db, você encontrará diversas tabelas que implementam os relacionamentos descritos no diagrama acima. Clique nos menus suspensos abaixo para saber mais sobre o esquema de cada tabela individual.

tabela districts (distritos)

A tabela districts contém as seguintes colunas:

  • id: identifica o id do distrito
  • name: o nome do distrito
  • type: que denota o tipo de distrito. Em Massachusetts, existem distritos escolares públicos (denominados “Public School District”) e distritos autônomos (denotados “Charter District”). Não há necessidade de entender a diferença entre os dois!
  • city: a cidade em que o distrito está localizado
  • state: o estado em que o distrito está localizado
  • zip: o ZIP (cep) do distrito
tabela schools (escolas)

A tabela drivers contém as seguintes colunas:

  • id: identifica o id da escola
  • name: o nome da escola
  • type: o tipo de escola (“Public School” ou “Charter School”)
  • city: a cidade em que a escola está localizada
  • state: o estado em que a escola está localizada
  • zip: o ZIP (cep) da escola
tabela graduation_rates (taxas de graduação)

A tabela graduation_rates contém as seguintes colunas:

  • id: identifica o id da taxa de graduação
  • school_id: o ID da escola associada à taxa de graduação
  • graduated: percentual de alunos,0-100, que se formaram no tempo certo
  • dropped: percentual de alunos,0-100, que se formaram no tempo certo
  • excluded: percentual de alunos,0-100, que foram expulsos
tabela expenditures (gastos)

A tabela scans contém as seguintes colunas:

  • id: identifica o id do gasto
  • district_id: id do distrito associado ao gasto
  • pupils: número de alunos que frequentam as escolas do distrito
  • per_pupil_expenditure: a quantia de dinheiro gasta por aluno que frequenta escolas no distrito
tabela staff_evaluations (avaliações das equipes)

A tabela staff_evaluations contém as seguintes colunas:

  • id: identifica o id do relatório de avaliação
  • district_id: id do distrito associado à avaliação
  • evaluated: porcentagem de avaliações das equipes dos distritos
  • exemplary: é a porcentagem de avaliações que marcaram as equipes do distrito como “exemplares” exemplary
  • proficient: é a porcentagem de avaliações que marcaram as equipes do distrito como “proficientes” proficient
  • needs_improvement: é a porcentagem de avaliações que marcaram as equipes do distrito como “precisam de melhoria” needs_improvement
  • unsatisfactory: é a porcentagem de avaliações que marcaram as equipes do distrito como “insatisfatória” unsatisfactory

OBS: Alguns dos relacionamentos do banco de dados podem ser descritos por uma tabela única. Mas você deve trabalhar com o banco de dados que a equipe usa!

Problema para resolver

Para cada uma das perguntas a seguir, você deve escrever uma única query SQL que produza os resultados especificados por cada problema. Sua resposta deve assumir a forma de uma única consulta SQL.

  1. Seu colega está preparando um mapa de todas as escolas públicas de Massachusetts. Escreva uma query SQL para encontrar os nomes e cidades de todas as escolas públicas de Massachusetts. OBS: Lembre que nem todas as escolas na tabela schools são consideradas escolas públicas.

  2. Sua equipe está trabalhando no arquivamento de dados antigos. Escreva uma query SQL para encontrar os nomes dos distritos que não estão mais operacionais. OBS:Distritos que não estão mais operacionais tem o “(non-op)” no final de seu nome.

  3. O Legislativo de Massachusetts gostaria de saber quanto dinheiro, em média, os distritos gastaram por aluno no ano passado. Escreva uma consulta SQL para encontrar o gasto médio por aluno. Nomeie a coluna como “Despesas Médias por Aluno do Distrito”.

    • Observe que a coluna per_pupil_expenditure na tabela de expenditures contém o valor médio, por aluno, que cada distrito gastou no ano passado. Encontre a média deste conjunto de médias, ponderando cada distrito de forma igual, independentemente do seu tamanho.

  4. Algumas cidades têm mais escolas públicas do que outras. Escreva uma query SQL para encontrar as 10 cidades com mais escolas públicas. Sua consulta deve retornar os nomes das cidades e o número de escolas públicas dentro delas, ordenados do maior para o menor número de escolas públicas. Se duas cidades tiverem o mesmo número de escolas públicas, ordene-as em ordem alfabética.

  5. O DEES gostaria que você determinasse em quais cidades poderiam ser necessárias escolas públicas adicionais. Escreva uma query para encontrar cidades com 3 ou menos escolas públicas (“Public Schools”). Sua consulta deve retornar os nomes das cidades e o número de escolas públicas dentro delas, ordenados do maior para o menor. Se duas cidades tiverem o mesmo número de escolas públicas, ordene-as de forma alfabética.

  6. O DEES quer avaliar quais escolas alcançaram uma taxa de graduação de 100%. Escreva uma query SQL para encontrar os nomes das escolas (públicas ou charter!) que relataram uma taxa de graduação de 100%.

  7. O DEESE está preparando um relatório sobre as escolas do distrito escolar de Cambridge. Escreva uma query SQL para encontrar os nomes das escolas (públicas ou charter!) no distrito escolar de Cambridge. Tenha em mente que Cambridge, a cidade, contém alguns distritos escolares, mas o DESE está interessado no distrito cujo nome é “Cambridge”.

  8. Um pai deseja enviar seu filho para um distrito que tenha muitos alunos. Escreva uma query SQL para exibir os nomes de todos os distritos escolares e o número de alunos matriculados em cada um.

  9. Outro pai deseja enviar seu filho para um distrito com poucos alunos. Escreva uma query SQL para encontrar o nome (ou nomes) do(s) distrito(s) escolar(es) com o menor número de alunos. Informe apenas o(s) nome(s).

  10. Em Massachusetts, as despesas do distrito escolar são em parte determinadas pelos impostos locais sobre valores de propriedade (por exemplo, casa). Escreva uma query SQL para encontrar os 10 distritos escolares públicos com os maiores gastos por aluno. A sua consulta deverá devolver os nomes dos distritos e as despesas por aluno de cada um.

  11. Existe uma relação entre despesas escolares e taxas de graduação? Escreva uma query SQL para exibir os nomes das escolas, suas despesas por aluno e sua taxa de graduação. Classifique as escolas da maior para a menor despesa por aluno. Se duas escolas tiverem a mesma despesa por aluno, ordene por nome da escola. (Assuma que a despesa por aluno de cada escola é a mesma que o seu distrito gasta como um todo).

  12. Um pai pede conselhos sobre como encontrar os melhores distritos escolares públicos em Massachusetts. Escreva uma query SQL para encontrar distritos escolares públicos com gastos por aluno acima da média e uma porcentagem acima da média de professores classificados como “exemplares”. A sua consulta deverá devolver os nomes dos distritos, juntamente com as suas despesas por aluno e a percentagem de professores classificados como exemplares. Classifique os resultados primeiro pela percentagem de professores classificados como exemplares (de alto a baixo), depois pela despesa por aluno (de alto a baixo).